﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace EntryExit
{
    public class SqlDBManager : ISqlDBManager
    {
        private DataSet dbDataSet { get; set; }
        public SqlDataAdapter dbDataAdapter { get; set; }

        public SqlDBManager()
        {
            dbDataSet = new DataSet();
            dbDataAdapter = new SqlDataAdapter();
        }

        public DataTable GetData(string commandName, CommandType commandType, DBName dbName, SqlParameter[] parameters)
        {
            using (SqlConnection connection = GetConnection(dbName))
            using (SqlCommand command = new SqlCommand(commandName){CommandType = commandType})
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                connection.Open();
                command.Connection = connection;
                return GetDataTable(command);
            }
        }

        public DataTable GetData(string commandName, CommandType commandType, DBName dbName)
        {
            return GetData(commandName, commandType, dbName, null);
        }

        public SqlConnection GetConnection(DBName dbName)
        {
            switch (dbName)
            {
                case DBName.SERVICE:
                    return new SqlConnection(ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_HRMS_CONNECTION));
                case DBName.CCFTEvent:
                    return new SqlConnection(ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_CARDAX_EVENT_CONNECTION));
                case DBName.CCFTCentral:
                    return new SqlConnection(ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_CARDAX_CENTRAL_CONNECTION));
                default:
                    return null;
            }
        }

        public DataTable GetDataTable(SqlCommand command)
        {
            command.CommandTimeout = Convert.ToInt32(ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_TIMEOUTVALUE));
            dbDataSet.Tables.Clear();
            dbDataAdapter.SelectCommand = command;
            dbDataAdapter.Fill(dbDataSet);
            return dbDataSet == null || dbDataSet.Tables.Count == 0 ? null : dbDataSet.Tables[0];
        }

        public DataSet GetData(string commandName, CommandType commandType, DBName dbName, SqlParameter[] parameters,string type)
        {
            using (SqlConnection connection = GetConnection(dbName))
            using (SqlCommand command = new SqlCommand(commandName) { CommandType = commandType })
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                connection.Open();
                command.Connection = connection;
                return GetDataSet(command);
            }
        }

        public DataSet GetDataSet(SqlCommand command)
        {
            command.CommandTimeout = Convert.ToInt32(ConfigMgr.Instance.GetValue(ConfigKeys.CNFG_TIMEOUTVALUE));
            dbDataSet.Tables.Clear();
            dbDataAdapter.SelectCommand = command;
            dbDataAdapter.Fill(dbDataSet);
            return dbDataSet;
        }
    }
}